package database;

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.ResultSetMetaData;
import java.sql.SQLException; 
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;


import structure.AssetNode;
import structure.Relationship;


/**
 * 程式資訊摘要 : MYSQL基本連結與查詢回傳
 * 版本資訊: 1.0
 * 程式設計人員姓名:朱聖池
 */

public class Jdbcmysql {
	int checknodecount = 0 ;
	private Connection con = null;
	private Statement stat = null;
	private ResultSet rs = null;
	private PreparedStatement pst = null;
	
	/**
	 * 基本資料庫連結初始設定
	 */
	public Jdbcmysql () {
		connectDb();
	}
	
	/**
	 * 設定資料庫連結test，以及user和password
	 */
	public void connectDb(){
		try {
	        Class.forName("com.mysql.jdbc.Driver"); 
	        //註冊driver 
	        con = DriverManager.getConnection( 
	        "jdbc:mysql://localhost/assetxml?useUnicode=true&characterEncoding=utf-8", 
	        "root","p@ssw0rd"); 
	        //取得connection
	 
		    //jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf-8
		    //localhost是主機名,test是database名
		    //useUnicode=true&characterEncoding=Big5使用的編碼 
	 
	    } 
	    catch(ClassNotFoundException e) 
	    { 
	      System.out.println("DriverClassNotFound :"+e.toString()); 
	    }//有可能會產生sqlexception 
	    catch(SQLException x) { 
	      System.out.println("Exception :"+x.toString()); 
	    } 
	}
	
	/**
	 * 建立Table智產特徵表格
	 */
	public void creatRelationsTable() {
		try {
			String createdbSQL = "CREATE TABLE IF NOT EXISTS relations (" + 
				    "    graphID     VARCHAR(255) " + 
				    "  , HeadNodeID    VARCHAR(255) " + 
				    "  , HeadNodeVersion    VARCHAR(255) " + 
				    "  , Relations    VARCHAR(255) " + 
				    "  , TailNodeID    VARCHAR(255) " + 
				    "  , TailNodeVersion    VARCHAR(255)) " ;

			stat = con.createStatement();
			stat.executeUpdate(createdbSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	/**
	 * 從XML擷取出的特徵邊存入DB
	 * @param grpahID
	 * @param assetID
	 * @param assetVersion
	 * @param relations
	 * @param assetID2
	 * @param assetVersion2
	 */
	public void insertRelations(String grpahID, String assetID,
			String assetVersion, String relations,
			String assetID2, String assetVersion2) {
		
		try{
			String insertSQL = "INSERT INTO relations " +
					"(graphID, HeadNodeID, HeadNodeVersion, Relations, "
					+ "TailNodeID, TailNodeVersion) " +
					"VALUES( '" + grpahID + "', '" + assetID + "', '" + assetVersion+ 
					"', '" + relations + "', '" + assetID2 +"', '" 
					+ assetVersion2 +"' )";
			stat = con.createStatement();
			stat.executeUpdate(insertSQL);
		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
		
	}
	/**
	 * 建立Table解決方案ID與解決方案名稱對照表
	 */
	public void creatSolutionTable() {
		try {
			String creatdbSQL = "CREATE TABLE IF NOT EXISTS solution (" +
					"ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,"+
					"SolutionId VARCHAR(255) NOT NULL ,"+
					"SolutionName VARCHAR(255) NOT NULL )";	
			stat = con.createStatement();
			stat.executeUpdate(creatdbSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	
	/**
	 * 新增解決方案名稱與解決方案ID至資料庫
	 * @param SolutionId 解決方案ID
	 * @param SolutionName 解決方案名稱
	 */
	public void insertSolution(String SolutionId, String SolutionName) {
		try{
			String insertSQL = "INSERT INTO solution " +
					"(Id, SolutionId, SolutionName) " +
					"VALUES( 0, '" + SolutionId + "', '" + SolutionName + "')";
			stat = con.createStatement();
			stat.executeUpdate(insertSQL);
		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
	}
	
	
	/**
	 * 刪除資料表
	 * @param tableName 所要刪除的資料表名稱
	 */
	public void dropTable(String tableName) {
		try {
			String dropdbSQL = "DROP TABLE IF EXISTS " + tableName;
			stat = con.createStatement();
			stat.executeUpdate(dropdbSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	
	/**
	 * 根據解決方案ID，刪除與此Id相關資料
	 * @param graphId 所要刪除的解決方案ID
	 */
	public void deleteSolution(String graphId){
		try {
			String deleteSQL = "delete from solution where SolutionId='" + graphId + "'";
			stat = con.createStatement();
			stat.executeUpdate(deleteSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	
	/**
	 * 根據解決方案ID，刪除與此Id相關資料
	 * @param graphId 所要刪除的解決方案ID
	 */
	public void deleteNode(String graphId){
		try {
			String deleteSQL = "delete from assetnode where graphID='" + graphId + "'";
			stat = con.createStatement();
			stat.executeUpdate(deleteSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	
	/**
	 * 根據解決方案ID，刪除與此Id相關資料
	 * @param graphId 所要刪除的解決方案ID
	 */
	public void deleteEdge(String graphId){
		try {
			String deleteSQL = "delete from relations where graphID='" + graphId + "'";
			stat = con.createStatement();
			stat.executeUpdate(deleteSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	
	/**
	 * 輸入解決方案ID取得解決方案的名稱
	 * @param SolutionId 解決方案ID
	 */
	public String getSolutionId(String SolutionId){
		String SolutionName = "";
		try {
			String selectSQL = "select * from solution where SolutionId='" + SolutionId + "'";
			stat = con.createStatement();
			rs = stat.executeQuery(selectSQL);
			while(rs.next()){
				SolutionName = rs.getString("SolutionName");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
		return SolutionName;
	}
	
	/**
	 * 關閉資料庫SQL指令連結
	 */
	public void closeSQL() {
		try {
			if(rs != null){
				rs.close();
				rs = null;
			}if(stat != null) {
				stat.close();
				stat = null;
			}if(pst != null) {
				pst.close();
				pst = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 關閉資料庫連結
	 */
	public void closedb() {
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 建立Node智產特徵資料
	 */
	public void creatNodeTable() {
		try {
			String creatdbSQL = "CREATE TABLE IF NOT EXISTS assetnode (" +
					"ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,"+
					"assetID VARCHAR(255) NOT NULL ,"+
					"assetName VARCHAR(255) NOT NULL ,"+
					"layer INTEGER ,"+
					"version VARCHAR(255) NOT NULL ,"+
					"assetTypeName VARCHAR(255) NOT NULL ,"+
					"shortDescription VARCHAR(255) NOT NULL ,"+
					"graphID VARCHAR(255) NOT NULL )";	
			stat = con.createStatement();
			stat.executeUpdate(creatdbSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeSQL();
		}
	}
	/**
	 * 新增node智產特徵至資料庫
	 * @param assetID 智產ID
	 * @param assetName 智產名字
	 * @param layer 智產階層
	 * @param assetTypeName 擷取的智產type
	 * @param shortDescription 擷取的智產特徵desciption
	 * @param graphID 紀錄從哪個圖出來 虛擬點ID
	 */
	public void insertNode(String assetID, String assetName, int layer, String version , String assetTypeName, String shortDescription, String graphID) {
		try{

			String insertSQL = "INSERT INTO assetnode (assetID, assetName, layer,version, assetTypeName, shortDescription, graphID) VALUES (?, ?, ?, ?, ?, ?, ?)";
			
			PreparedStatement preStmt = null;
			preStmt = con.prepareStatement(insertSQL);
		   
			preStmt.setString(1, assetID);
			preStmt.setString(2, assetName);
			preStmt.setInt(3, layer);
			preStmt.setString(4, version);
			preStmt.setString(5, assetTypeName);
			preStmt.setString(6, shortDescription);
			preStmt.setString(7, graphID);
		   
			preStmt.executeUpdate();

		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
	}
	/**
	 * 回傳SQL所有的solution
	 * @return String[][]
	 */
	public String[][] catchSolution(){
		try{
			String sql="SELECT SolutionId,SolutionName FROM solution";//Query語句
			stat = con.createStatement();
			ResultSet rs = stat.executeQuery(sql);//Query結果存在這裡
			boolean isHasData = rs.last();
			//如果回傳有資料才做，沒有則不做
			if(isHasData){
		    	ResultSetMetaData rsmd = rs.getMetaData(); //取得Query資料
		    	int Columnline = rsmd.getColumnCount();
		    	
		        String[][] Solution= new String[rs.getRow()][Columnline] ;
		        rs.first();
		        do{//while loop 一筆一筆iterate
		        	int localindex = rs.getRow()-1;
		        	Solution[localindex][0] = rs.getString(rsmd.getColumnLabel(1));
		        	Solution[localindex][1] = rs.getString(rsmd.getColumnLabel(2));	
		        	//System.out.println(Solution[localindex][0]+","+Solution[localindex][1]);//印出資料
		        }while(rs.next());
		        return Solution ;
		        
			}
		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
		return null;
	}
	/**
	 * 從relations table找出跟此Solution有關的邊點及其關係
	 * @param SolutionID
	 * @return 該solution之edge
	 */
	public ArrayList<Relationship> searchEdge(String SolutionID){
		try{
			ArrayList<Relationship> graphEdge = new ArrayList<Relationship>();
			String sql="SELECT * FROM relations Where graphID = '"+SolutionID+"'";//Query語句
			stat = con.createStatement();
			ResultSet rs = stat.executeQuery(sql);//Query結果存在這裡
			boolean isHasData = rs.last();
			//如果回傳有資料才做，沒有則不做
			if(isHasData){
		    	ResultSetMetaData rsmd = rs.getMetaData(); //取得Query資料    	
		    	//Relationship extracting
		        rs.first();
		        do{//while loop 一筆一筆iterate
		        	Relationship existEdge = new Relationship();
		        	AssetNode headnode = new AssetNode();
		        	AssetNode tailnode = new AssetNode();
		        	headnode.setGrpahID(rs.getString(rsmd.getColumnLabel(1)));
		        	headnode.setAssetID(rs.getString(rsmd.getColumnLabel(2)));
		        	headnode.setVersion(rs.getString(rsmd.getColumnLabel(3)));
		        	existEdge.setRelations(rs.getString(rsmd.getColumnLabel(4)));
		        	tailnode.setGrpahID(rs.getString(rsmd.getColumnLabel(1)));
		        	tailnode.setAssetID(rs.getString(rsmd.getColumnLabel(5)));
		        	tailnode.setVersion(rs.getString(rsmd.getColumnLabel(6)));
		        	existEdge.setHead(headnode);
		        	existEdge.setTail(tailnode);
		        	
		        	//existEdge.showRelations();
		        	graphEdge.add(existEdge);
		        }while(rs.next());
		        
		        return graphEdge;
		        
			}
		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
		return null;
	}
	/**
	 * 
	 * @param SolutionID
	 * @return 該solution之node
	 */
	public HashMap<String, AssetNode> searchNode(String SolutionID) {
		try{
			HashMap<String,AssetNode> graphNode = new HashMap<String,AssetNode>();
			String sql="SELECT * FROM assetnode Where graphID = '"+SolutionID+"'";//Query語句
			stat = con.createStatement();
			ResultSet rs = stat.executeQuery(sql);//Query結果存在這裡
			boolean isHasData = rs.last();
			//如果回傳有資料才做，沒有則不做
			if(isHasData){
		    	ResultSetMetaData rsmd = rs.getMetaData(); //取得Query資料    	
		    	//Relationship extracting
		        rs.first();
		        do{//while loop 一筆一筆iterate   	
		        	AssetNode Node = new AssetNode();
		        	
		        	Node.setGrpahID(rs.getString(rsmd.getColumnLabel(8)));
		        	Node.setAssetID(rs.getString(rsmd.getColumnLabel(2)));
		        	Node.setAssetName(rs.getString(rsmd.getColumnLabel(3)));
		        	Node.setAssetLayerType(Integer.parseInt(rs.getString(rsmd.getColumnLabel(4))));
		        	Node.setVersion(rs.getString(rsmd.getColumnLabel(5)));
		        	Node.setAssetLayer(nodeLayer(Integer.parseInt(rs.getString(rsmd.getColumnLabel(4)))));
		        	Node.setAssetTypeName(rs.getString(rsmd.getColumnLabel(6)));
		        	Node.setShortDescription(rs.getString(rsmd.getColumnLabel(7)));
		        	
		        	graphNode.put(rs.getString(rsmd.getColumnLabel(2))+"_"+rs.getString(rsmd.getColumnLabel(5)), Node);
		        }while(rs.next());
		        
		        return graphNode;
		        
			}
		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
		return null;
	}
	/**
	 * 
	 * @param layer
	 * @return layerName
	 */
	public String nodeLayer(int layer){
		if(layer==1){
			return "Strategy";
		}
		else if(layer==2){
			return "Operation";
		}
		else if(layer==3){
			return "Execution";
		}
		else if(layer==4){
			return "Implementation";
		}
		return null;
		
	}
	/**
	 * 刪除此GRAPH
	 * @param Id
	 * @param version
	 */
	public void deleteGraphID(String Id,String version){
		deleteSolution(Id+"_"+version);
		deleteNode(Id);
		deleteEdge(Id);
	}
	
	/**
	 * @return HashMap<String, AssetNode>
	 */
	public HashMap<String, AssetNode> getAssetNode() {
		try{
			HashMap<String,AssetNode> graphNode = new HashMap<String,AssetNode>();
			String sql="SELECT * FROM assetnode";//Query語句
			stat = con.createStatement();
			ResultSet rs = stat.executeQuery(sql);//Query結果存在這裡
			boolean isHasData = rs.last();
			//如果回傳有資料才做，沒有則不做
			if(isHasData){
		    	ResultSetMetaData rsmd = rs.getMetaData(); //取得Query資料    	
		    	//Relationship extracting
		        rs.first();
		        do{//while loop 一筆一筆iterate   	
		        	AssetNode Node = new AssetNode();
		        	
		        	Node.setGrpahID(rs.getString(rsmd.getColumnLabel(8)));
		        	Node.setAssetID(rs.getString(rsmd.getColumnLabel(2)));
		        	Node.setAssetName(rs.getString(rsmd.getColumnLabel(3)));
		        	Node.setAssetLayerType(Integer.parseInt(rs.getString(rsmd.getColumnLabel(4))));
		        	Node.setVersion(rs.getString(rsmd.getColumnLabel(5)));
		        	Node.setAssetLayer(nodeLayer(Integer.parseInt(rs.getString(rsmd.getColumnLabel(4)))));
		        	Node.setAssetTypeName(rs.getString(rsmd.getColumnLabel(6)));
		        	Node.setShortDescription(rs.getString(rsmd.getColumnLabel(7)));
		        	
		        	graphNode.put(rs.getString(rsmd.getColumnLabel(2))+"_"+rs.getString(rsmd.getColumnLabel(5)), Node);
		        }while(rs.next());
		        
		        return graphNode;
		        
			}
		}catch(SQLException e){
			e.printStackTrace(); 
		}finally{
			closeSQL();
		}
		return null;
	}
}
